﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace LANmine
{
    /// <summary>
    /// LANmine Database Handler
    /// </summary>
    public class Database
    {
        /** Read-Only User */
        private String ROUser = "NietVoorTienUur_Read";
        private String ROPass = "NVTU_Read1";

        /** Write User */
        private String RWUser = "NietVoorTienUur_Write";
        private String RWPass = "NVTU_Write1";

        /** Database Server */
        private UInt16 DBTimeout = 3;
        private String DBServer = "sql.ti.hubrussel.be,15975";
        private String LMDatabase = "SODO_NietVoorTienUur";

        /** Connection Objects */
        private String dbConStr = "";
        private SqlConnection dbConn;

        /**
         * Default Constructor
         * Builds the connection string to the LANmine database.
         * 
         * param name="mode"
         */
        public Database(String mode)
        {
            /** Create the base of the connection string */
            this.dbConStr = "server=" + this.DBServer + "; Trusted_Connection=no; database=" + this.LMDatabase + "; connection timeout=" + this.DBTimeout + ";";

            /** Check the connection mode */
            switch (mode)
            {
                case "ro":
                    /** Append Read-Only SQL user */
                    this.dbConStr += "user id=" + this.ROUser + "; password=" + this.ROPass;
                    break;
                case "rw":
                    /** Create Read-Only SQL user */
                    this.dbConStr += "user id=" + this.RWUser + "; password=" + this.RWPass;
                    break;
                default:
                    throw new ArgumentOutOfRangeException("mode", null, "Database connection mode " + mode + " does not exist.");
            }

            /** Create the SQL connection object */
            this.dbConn = new SqlConnection(this.dbConStr);
        }


        /**
         * ******************************
         * * Connected Database Methods *
         * ******************************
         * 
         * To read data from a database, you need a data reader and a connection object.
         * The connection must first be opened, then read the data, then close the connection.
         * 
         * TODO: Automatically close the connection in this class.
         */
        public SqlConnection getDbConn()
        {
            /** Return the newly created database connection */
            return this.dbConn;
        }

        public SqlDataReader getDbReader(String sqlQry)
        {
            /** Pass the query on to the database connection */
            SqlCommand dbCom = new SqlCommand(sqlQry, this.dbConn);

            return dbCom.ExecuteReader();
        }

        /**
         * Database::checkQuery
         * Checks if a given query string gives any result.
         * Useful for user interaction / tactile feedback.
         */
        public Boolean checkQuery(String cQry)
        {
            /** Return value */
            bool rVal;
            /** Get SQL connection */
            SqlConnection dbConn = this.getDbConn();

            try
            {
                /** Open connection */
                dbConn.Open();
                /** Get DB reader */
                SqlDataReader dbRead = this.getDbReader(cQry);

                /** Check for results */
                if (dbRead.HasRows)
                    rVal = true;
                else
                    rVal = false;

                /** Close DB connection */
                dbConn.Close();
            }
            catch
            {
                dbConn.Close();
                throw;
            }
            /** Return the result */
            return rVal;
        }
    }
}